import openpyxl
import os


def find_text_in_excel(file_path, target_text):
    workbook = openpyxl.load_workbook(file_path, data_only=True)
    results = []

    # 遍历每个工作表
    for sheet_name in workbook.sheetnames:
        sheet = workbook[sheet_name]
        # 获取列名，即第一行的内容
        column_names = [cell.value for cell in sheet[1]]

        for row in sheet.iter_rows(min_row=2):  # 从第二行开始，因为第一行是列名
            for cell in row:
                # 如果找到目标文本，保存整行内容
                if cell.value and target_text in str(cell.value):
                    row_content = {column_names[cell.column - 1]: cell.value for cell in row}
                    results.append({
                        'File': file_path,
                        'Sheet': sheet_name,
                        'Row': cell.row,
                        'Content': row_content
                    })
                    break  # 因为我们已经处理了这一行，所以跳出内层循环

    return results


def grep_file(key_word):
    directory_path = "任务管理文档/"
    target_text = key_word
    with open(f"result/{key_word}.txt","w", encoding="utf-8") as fp:
        all_results = []

        # 遍历指定目录下的所有文件
        for filename in os.listdir(directory_path):
            if filename.endswith('.xlsx'):
                file_path = os.path.join(directory_path, filename)
                all_results.extend(find_text_in_excel(file_path, target_text))

        if all_results:
            for entry in all_results:
                fp.write(f"在文件 '{entry['File']}' 的工作表 '{entry['Sheet']}' 中，行 {entry['Row']} 找到匹配内容。\n")
                for col_name, value in entry['Content'].items():
                    fp.write(f"{col_name}: {value}\t")
                fp.write("\n")
        else:
            print(f"在文件夹中未找到包含 '{target_text}' 的文本。")


if __name__ == "__main__":
    names = []
    for name in names:
        grep_file(name)
